#include <stdio.h>
#include <stdlib.h>
#include <sqlite3.h>
#include <Python.h>
#include <string.h>
#include <locale.h>
#include "functions.h"

char* fileName = "statistics.txt";

//Create a new file or Clean an existing file
void cleanFile()
{
	FILE* file;
	file = fopen( fileName, "w" );
	close(file);
}

//Receive a PyObject that indicates whether to sort by year or by number of teachers
//returns a list with the number of teachers already ordered
PyObject* nDocentesPerYear(PyObject* orderBy){

	int i = 0;
	char bd[16] = "";
	int contagem;
	char* sql = "SELECT COUNT(*) FROM docentes";
	struct NumberPerYear array[10];

	PyObject* list = PyList_New(0);
	PyObject* tuplo;
	
	
	for(i = 0; i <= 9; i++)
	{
		//Creates the path to the databases
		strcpy(bd, bdName(i));

		//Open BD, do query and Close BD
		openBD (bd,sql,callbackInt, &contagem);
		//printf("%d", NotUsed);

		//Creates an array of structures to carry out the qsort per year or per nº Teachers
		struct NumberPerYear temp;
		temp.year = i;
		temp.number = contagem;
		array[i] = temp;
	}

	//Organizes the data according to user choice
	//in this case the user may choose to organize NDOCENTES (nº of teachers)
	//or by year, this is default
	if(strcmp((PyString_AsString(orderBy)),"NDOCENTES") == 0)
	{
		qsort(array,10,sizeof(struct NumberPerYear), comparenNumberPerYear);
	}

	//Save to File the statistics
	FILE* file;
	file = fopen( fileName, "a" );
	fprintf(file, "########################################################\n");
	fprintf(file, "#            Number of Teachers per Year               #\n");
	fprintf(file, "########################################################\n\n");
	
	for(i = 0; i < 10; i++)
	{
		tuplo = PyTuple_New(2);
		fprintf(file, "Ano - 200%d -- Ndocentes -- %d\n", array[i].year, array[i].number);

		//set information on the tuplo
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(array[i].year));
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyInt_FromLong(array[i].number));
		
		PyList_Append(list, tuplo);//append the organized values on the PyList
	}
	
	fprintf(file,"\n\n");
	fclose(file);
	return list;
}

//list of institutions per year
//return a PyList with the institutions
PyObject* listInstYear(PyObject* yearList)
{
	int i = 0;
	int j = 0;
	char bd[16] = "";
	char* sql = "SELECT DISTINCT estabelecimento FROM fichas_docencia ORDER BY estabelecimento";
	PyObject* listTotal = PyList_New(0);
	PyObject* list;
	PyObject* tuplo;
	
	FILE* file;
	file = fopen( fileName, "a" );
	fprintf(file, "########################################################\n");
	fprintf(file, "#          List of Institutions per Year               #\n");
	fprintf(file, "########################################################\n\n");
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		list = PyList_New(0);//New PyList for each year
		tuplo = PyTuple_New(2);//New PyTuple for each year

		//Put the year on the tuple
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), 
		                PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));

		//Cria o caminho para as bases de dados
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));

		//Abrir BD, Efectuar Query e Fechar BD
		openBD (bd,sql,callbackPyList, &list);

		int listSize = PyList_Size(list);

		//create an array with PyObjects
    	PyObject **arrayPyObject = malloc( sizeof(PyObject*) * listSize );
		
		//copy the list elements to the array
		for(j = 0; j < listSize; j++)
		{		
			arrayPyObject[j] = PyList_GetItem(list, j);
    	}
		
		//order with qsort
		qsort(arrayPyObject, listSize, sizeof(PyObject*), PyStringCmp);
		
		//insert sorted items into pylist again
    	for (j = 0; j < listSize; j++)
		{
        	PyList_SetItem(list, j, arrayPyObject[j]);
    	}

		//Append a list with the institutions on the tuplo
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), list);

		//write on file the year
		fprintf(file, "\nAno - 200%d\n", PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i)))));

		//write the institutions on file
		for(j = 0; j < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(
		      PyTuple_GetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)))))); j++)
		{
			fprintf(file, "%s\n", PyString_AsString(PyList_GET_ITEM(PyTuple_GetItem(
			      		tuplo, PyInt_AsSsize_t(PyInt_FromLong(1))), PyInt_AsSsize_t(PyInt_FromLong(j)))));
		}
	
		//append the tuplo of year and instituitions of each year
		PyList_Append(listTotal, tuplo);
	}

	fprintf(file,"\n\n");
	fclose(file);
	
	return listTotal;	
}

//Show the number of teacher per Institution per year
//Create a File with this information
//Returns a list of tuples with the information
PyObject* nDocentesPerInstPerYear(PyObject* yearList)
{
	int i = 0;
	int j = 0;
	int nInst = 0;
	char bd[16] = "";
	char* sql = "SELECT DISTINCT estabelecimento, COUNT(docente) FROM fichas_docencia GROUP BY estabelecimento";
	PyObject* finalList = PyList_New(0); //List with tuples with year, institution, nº teachers
	PyObject* listnInst = PyList_New(PyList_Size(yearList)); //list of number of instituitions per year
	PyObject* tuplo;

	struct nDocentesPerInstPerYear* nDIY;
	struct nDocentesPerInstPerYear* free_nDIY;

	FILE* file;
	file = fopen( fileName, "a" );
	fprintf(file, "########################################################\n");
	fprintf(file, "#      Number of Teachers per Institution per Year     #\n");
	fprintf(file, "########################################################\n\n");

	//fecth the number of institution in yearsList, to allocate memory
	//for the array of strucks
	//list is orderBy year
	listnInst = nInstYear(yearList, PyString_FromFormat("%s", "c"));

	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		nInst = PyInt_AS_LONG(PyTuple_GetItem(PyList_GET_ITEM(listnInst, 
		                                                      PyInt_AsSsize_t(PyInt_FromLong(i))),PyInt_AsSsize_t(PyInt_FromLong(1))));

		//creates the path to the database
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));		

		nDIY = malloc(nInst*sizeof(struct nDocentesPerInstPerYear));

		free_nDIY = nDIY;

		//open database and do the query
		openBD(bd, sql, callBack_NDIY, &nDIY);

		//arrange the items in the array
		qsort(free_nDIY, nInst, sizeof(struct nDocentesPerInstPerYear),compNDocentesPerInstPerYear);		

		//write on file the year
		fprintf(file, "\nAno - 200%d\n", PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i)))));

		//Insert Instituitions and the number of teachers in list of tuples
		//free memory from struct
		for(j = 0; j < nInst; j++)
		{
			tuplo = PyTuple_New(3);
			//Put the year on the tuple
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), 
			                PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));

			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",free_nDIY[j].inst));
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(2)), PyInt_FromLong(free_nDIY[j].nDocentes));

			//Write in file
			fprintf(file, "%s --", free_nDIY[j].inst);
			fprintf(file, "%d\n", free_nDIY[j].nDocentes);

			free(free_nDIY[j].inst);
			PyList_Append(finalList, tuplo);
		}
		free(free_nDIY);
	}		

	fprintf(file,"\n\n");
	fclose(file);
	return finalList;
}

//Show the number of teacher per Degree per year
//Create a File with this information
//Returns a list of tuples with the information
PyObject* nDocentesPerDegreePerYear(PyObject* yearList)
{
	int i = 0;
	int j = 0;
	int nDegree = 0;
	char bd[16] = "";
	char* sql = "SELECT DISTINCT grau, COUNT(docente) FROM fichas_docencia GROUP BY grau";
	PyObject* finalList = PyList_New(0); //List with tuples with year, institution, nº teachers
	PyObject* listnDegree = PyList_New(PyList_Size(yearList)); //list of number of degree per year
	PyObject* tuplo;

	struct nDocentesPerInstPerYear* nDIY;
	struct nDocentesPerInstPerYear* free_nDIY;

	FILE* file;
	file = fopen( fileName, "a" );
	fprintf(file, "########################################################\n");
	fprintf(file, "#        Number of Teachers per Degree per Year        #\n");
	fprintf(file, "########################################################\n\n");

	//fecth the number of degrees in the year in yearsList, to allocate memory
	//for the array of strucks
	//list is orderBy year
	listnDegree = nDegreeYear(yearList);

	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		nDegree = PyInt_AS_LONG(PyTuple_GetItem(PyList_GET_ITEM(listnDegree, 
		                                     PyInt_AsSsize_t(PyInt_FromLong(i))),PyInt_AsSsize_t(PyInt_FromLong(1))));

		//creates the path to the database
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));		

		nDIY = malloc(nDegree*sizeof(struct nDocentesPerInstPerYear));

		//save the reference of the first element of the array to use later
		//its necessary to save because the callback function moves the pointer
		//to the end of the array
		free_nDIY = nDIY;

		//open database and do the query
		openBD(bd, sql, callBack_NDIY, &nDIY);

		//arrange the items in the array
		qsort(free_nDIY, nDegree, sizeof(struct nDocentesPerInstPerYear),compNDocentesPerInstPerYear);		

		//write on file the year
		fprintf(file, "\nAno - 200%d\n", PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i)))));

		//Insert degree and the number of teachers in list of tuples
		//free memory from struct
		for(j = 0; j < nDegree; j++)
		{
			tuplo = PyTuple_New(3);
			//Put the year on the tuple
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), 
			                PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));

			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",free_nDIY[j].inst));
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(2)), PyInt_FromLong(free_nDIY[j].nDocentes));

			//write to file				
			fprintf(file, "%s --", free_nDIY[j].inst);
			fprintf(file, "%d\n", free_nDIY[j].nDocentes);

			free(free_nDIY[j].inst);
			PyList_Append(finalList, tuplo);
		}
		free(free_nDIY);
	}		

	fprintf(file,"\n\n");
	fclose(file);
	return finalList;
}

//list of holder of a degree per year
//return a PyList with the information
PyObject* listDegreeHolder(PyObject* yearList, PyObject* degree)
{
	int i = 0;
	int j = 0;
	char bd[16] = "";
	char* sql = malloc((65 + strlen(PyString_AsString(degree)))*sizeof(char));
	strcpy(sql, "SELECT DISTINCT docente FROM fichas_docencia WHERE grau=");
	strcat(sql, "'");
	strcat(sql, PyString_AsString(degree));
	strcat(sql, "'");
	
	PyObject* listTotal = PyList_New(0);
	PyObject* list;
	PyObject* tuplo;
	
	FILE* file;
	file = fopen( fileName, "a" );
	fprintf(file, "########################################################\n");
	fprintf(file, "#            List of Holders of a Degree               #\n");
	fprintf(file, "#            Degree Choosen: %s                         \n", PyString_AsString(degree));
	fprintf(file, "########################################################\n\n");
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		list = PyList_New(0);//New PyList for each year
		tuplo = PyTuple_New(2);//New PyTuple for each year

		//Put the year on the tuple
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), 
		                PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));

		//Cria o caminho para as bases de dados
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));

		//Abrir BD, Efectuar Query e Fechar BD
		openBD (bd,sql,callbackPyList, &list);

		int listSize = PyList_Size(list);

		//create an array with PyObjects
    	PyObject **arrayPyObject = malloc( sizeof(PyObject*) * listSize );
		
		//copy the list elements to the array
		for(j = 0; j < listSize; j++)
		{		
			arrayPyObject[j] = PyList_GetItem(list, j);
    	}
		
		//order with qsort
		qsort(arrayPyObject, listSize, sizeof(PyObject*), PyStringCmp);
		
		//insert sorted items into pylist again
    	for (j = 0; j < listSize; j++)
		{
        	PyList_SetItem(list, j, arrayPyObject[j]);
    	}
		
		//Append a list with the teachers on the tuplo
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), list);

		//write on file the year
		fprintf(file, "\nAno - 200%d\n", PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i)))));

		//write the institutions on file
		for(j = 0; j < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(
		      PyTuple_GetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)))))); j++)
		{
			fprintf(file, "%s\n", PyString_AsString(PyList_GET_ITEM(PyTuple_GetItem(
			      		tuplo, PyInt_AsSsize_t(PyInt_FromLong(1))), PyInt_AsSsize_t(PyInt_FromLong(j)))));
		}
		free(arrayPyObject);
		//append the tuplo of year and instituitions of each year
		PyList_Append(listTotal, tuplo);
	}

	fprintf(file, "\n\n");
	fclose(file);
	
	return listTotal;	
}

//Show the number of teacher per Degree per Institution Per year
//Create a File with this information
//Returns a list of tuples with the information
PyObject* nDocentesPerDegreePerInstPerYear(PyObject* yearList)
{
	int i = 0;
	int j = 0;
	char bd[16] = "";
	char* sql = "SELECT DISTINCT estabelecimento, grau, COUNT(docente) FROM fichas_docencia GROUP BY estabelecimento, grau";
	PyObject* finalList = PyList_New(0); //List with tuples with year, institution, nº teachers
	PyObject* tuplo;

	FILE* file;
	file = fopen( fileName, "a" );
	fprintf(file, "########################################################\n");
	fprintf(file, "#Number of teachers per Degree per Institution Per Year#\n");
	fprintf(file, "########################################################\n\n");

	int* arrayDegree = nDegreeInstYear(yearList);
	struct nDocentesPerDegreePerInstPerYear* nDIY;
	struct nDocentesPerDegreePerInstPerYear* free_nDIY;

	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(yearList))); i++)
	{
		nDIY = malloc((arrayDegree[i])*sizeof(struct nDocentesPerDegreePerInstPerYear));
		free_nDIY = nDIY;

		//creates the path to the database
		strcpy(bd, bdName(PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))))));		

		//open database and do the query
		openBD(bd, sql, callBack_NDDIY, &nDIY);

		//arrange the items in the array
		qsort(free_nDIY, arrayDegree[i], sizeof(struct nDocentesPerDegreePerInstPerYear),compNDocentesPerDegreePerInstPerYear);		

		//write on file the year
		fprintf(file, "Ano - 200%d\n", PyInt_AS_LONG(PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i)))));

		//Insert information and print file
		for(j = 0; j < arrayDegree[i]; j++)
		{
			tuplo = PyTuple_New(4);

			//Put the year on the tuple
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), 
			                PyList_GET_ITEM( yearList, PyInt_AsSsize_t(PyInt_FromLong(i))));

			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyString_FromFormat("%s",free_nDIY[j].inst));
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(2)), PyString_FromFormat("%s",free_nDIY[j].degree));
			PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(3)), PyInt_FromLong(free_nDIY[j].nDocentes));

			//write to file
			fprintf(file, "%s -- ", free_nDIY[j].inst);
			fprintf(file, "%s -- ", free_nDIY[j].degree);
			fprintf(file, "%d\n", free_nDIY[j].nDocentes);

			free(free_nDIY[j].inst);
			free(free_nDIY[j].degree);
			PyList_Append(finalList, tuplo);
		}
		free(free_nDIY);
	}
	free(arrayDegree);

	fprintf(file, "\n\n");
	fclose(file);
	return finalList;
}

//Information about teachers hiring
//return a PyList with tuples(year, hiring) with the information
PyObject* teacherHiring(PyObject* inst)
{
	int i = 0;
	int j = 0;
	char bd[16] = "";
	int contagem = 0;
	int contTemp = 0;
	PyObject* listTotal = PyList_New(0);
	PyObject* tuplo;

	//create the sql query
	char* sql = malloc((100+strlen(PyString_AsString(inst)))*sizeof(char));
	strcpy(sql, "SELECT count(docente) FROM fichas_docencia WHERE estabelecimento=");
	strcat(sql, "'");
	strcat(sql, PyString_AsString(inst));
	strcat(sql, "'");
	
	FILE* file;
	file = fopen( fileName, "a" );

	fprintf(file, "####################################################\n");
	fprintf(file, "#              Teachers Hirings                    #\n");
	fprintf(file, "#       Institution Choosen: %s                    \n", PyString_AsString(inst));
	fprintf(file, "####################################################\n\n");
	
	for(i = 0; i < 10; i++)
	{
		tuplo = PyTuple_New(2);//New PyTuple for each year

		//Put the year on the tuple
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), PyInt_FromLong(i));

		//Cria o caminho para as bases de dados
		strcpy(bd, bdName(i));

		//Abrir BD, Efectuar Query e Fechar BD
		openBD (bd,sql,callbackInt, &contagem);

		//calculates the difference of number of teachers and put on the tuple
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), PyInt_FromLong((contagem - contTemp)));

		//write on file
		fprintf(file, "Ano - 200%d\n", i);
		if(contagem == 0)
		{
			fprintf(file, "Neste Ano não Existiu esta Instituição\n");
		}
		else if((contagem - contTemp) < 0)
		{
			fprintf(file, "Foram Despedidos %d professores\n", (-1*(contagem - contTemp)));
		}
		else if((contagem - contTemp) == 0)
		{
			fprintf(file, "Não houve despedimentos nem contratações\n");
		}
		else
		{
			fprintf(file, "Foram contratados %d professores\n", (contagem - contTemp));
		}
	
		//append the tuplo of year and instituitions of each year
		PyList_Append(listTotal, tuplo);

		//save number o teacher in a temporary variable to compare next year
		contTemp = contagem;
	}

	fprintf(file, "\n\n");
	fclose(file);
	
	return listTotal;	
}

//Teacher hiring from a all institutions
//uses listInstNoWrite function to receive a list of Institutions
//uses teacherHiring to receive the calculation from each institution
//Return a list with tuples (Institution, List(Tuples(year, number)))
PyObject* teacherHiringPerInst()
{
	int i = 0;
	int j = 0;
	
	PyObject* listTotal = PyList_New(0);
	PyObject* listInst;
	PyObject* listTeacherHirings;
	PyObject* tuplo;

	listInst = listInstNoWrite(PyInt_FromLong(0));
	
	
	for(i = 0; i < PyInt_AsLong(PyInt_FromSsize_t(PyList_Size(listInst))); i++)
	{

		tuplo = PyTuple_New(2);
		listTeacherHirings = PyList_New(0);
		
		//Put the Institution on the tuple
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(0)), 
		                PyList_GET_ITEM( listInst, PyInt_AsSsize_t(PyInt_FromLong(i))));

		listTeacherHirings = teacherHiring(PyList_GET_ITEM( listInst, PyInt_AsSsize_t(PyInt_FromLong(i))));

		//Put the Teacher Hirings list on the tuple
		PyTuple_SetItem(tuplo, PyInt_AsSsize_t(PyInt_FromLong(1)), listTeacherHirings);


		//append the tuplo of year and instituitions of each year
		PyList_Append(listTotal, tuplo);
	}
	
	return listTotal;	
}
